package cnki.kg.demo;

import cnki.kg.demo.entity.CategoryNode;
import cnki.kg.demo.util.DateUtil;
import cnki.kg.demo.util.ExcelUtil;
import cnki.kg.demo.util.StringUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.jupiter.api.Test;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.test.context.web.WebAppConfiguration;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;
import java.util.stream.Collectors;


@SpringBootTest
@WebAppConfiguration
public class CreateSZRWCategory {
    @Autowired
    @Qualifier("mysqlJdbcTemplate")
    JdbcTemplate mysqlJdbcTemplate;
    @Autowired
    @Qualifier("ctwhJdbcTemplate")
    JdbcTemplate ctwhJdbcTemplate;
    @Autowired
    @Qualifier("szrwJdbcTemplate")
    JdbcTemplate szrwJdbcTemplate;

    @Test
    public void createRw() throws IOException {
        String filePath = "D:\\项目\\数字人文\\数字人文分类人物.xlsx";
        File file = new File(filePath);
        String fileName = file.getName();
        InputStream in = null;
        in = new FileInputStream(file);
        Workbook workbook = null;
        if (ExcelUtil.isExcel2007(fileName)) {
            workbook = new XSSFWorkbook(in);
        } else {
            workbook = new HSSFWorkbook(in);
        }
        // 有多少个sheet
        int sheets = workbook.getNumberOfSheets();
        List<String> cdList = new ArrayList<>();
        List<String> dwList = new ArrayList<>();
        List<String> rwList = new ArrayList<>();
        List<HashMap<String, String>> mps = new ArrayList<>();
        for (int i = 0; i < sheets; i++) {
            Sheet sheet = workbook.getSheetAt(i);
            int rowSize = sheet.getPhysicalNumberOfRows();
            for (int j = 0; j < rowSize; j++) {
                if (j == 0) continue;
                Row row = sheet.getRow(j);
                int cellSize = row.getPhysicalNumberOfCells();
                HashMap<String, String> mp = new HashMap<>();
                String cd = "";
                String dw = "";
                String rw = "";
                if (row.getCell(0) != null) {
                    row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
                    Cell cell0 = row.getCell(0);
                    cd = cell0.getStringCellValue();
                    mp.put("朝代", cd);
                }
                if (row.getCell(1) != null) {
                    row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                    Cell cell1 = row.getCell(1);
                    dw = cell1.getStringCellValue();
                    if (StringUtil.isBlank(dw)) {
                        dw = cd;
                    }
                    mp.put("社会地位", dw);
                }
                if (row.getCell(2) != null) {
                    row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
                    Cell cell2 = row.getCell(2);
                    rw = cell2.getStringCellValue();
                    mp.put("人物", rw);
                }
                mps.add(mp);
            }
        }
        in.close();
        cdList = mps.stream().map(n -> n.get("朝代")).distinct().collect(Collectors.toList());

        if (cdList == null || cdList.size() == 0) return;
        for (String m : cdList) {
            CategoryNode submitItem = new CategoryNode();
            submitItem.setCategoryNodeName(m);
            submitItem.setParentid(1);
            submitItem.setLevel(1);
            submitItem.setIsLeaf(0);
            submitItem.setFormula("");
            Integer id = insertCategoryNode(submitItem);
            dwList = mps.stream().filter(n -> n.get("朝代").equals(m)).map(n -> n.get("社会地位")).distinct().collect(Collectors.toList());
            for (String dw : dwList) {
                CategoryNode submitItem2 = new CategoryNode();
                submitItem2.setCategoryNodeName(dw);
                submitItem2.setParentid(id);
                submitItem2.setLevel(2);
                submitItem.setIsLeaf(0);
                submitItem2.setFormula("");
                Integer dwid = insertCategoryNode(submitItem2);
                rwList = mps.stream().filter(n -> n.get("朝代").equals(m) && n.get("社会地位").equals(dw)).map(n -> n.get("人物")).distinct().collect(Collectors.toList());
                for (String rw : rwList) {
                    CategoryNode submitItem3 = new CategoryNode();
                    submitItem3.setCategoryNodeName(rw);
                    submitItem3.setParentid(dwid);
                    submitItem3.setLevel(3);
                    submitItem.setIsLeaf(1);
                    submitItem3.setFormula(String.format("主题=\\'%s\\'", rw));
                    insertCategoryNode(submitItem3);
                }
            }
        }
    }

    @Test
    public void createGz() throws IOException {
        String filePath = "D:\\项目\\数字人文\\数字人文分类官职.xlsx";
        File file = new File(filePath);
        String fileName = file.getName();
        InputStream in = null;
        in = new FileInputStream(file);
        Workbook workbook = null;
        if (ExcelUtil.isExcel2007(fileName)) {
            workbook = new XSSFWorkbook(in);
        } else {
            workbook = new HSSFWorkbook(in);
        }
        // 有多少个sheet
        int sheets = workbook.getNumberOfSheets();
        List<String> cdList = new ArrayList<>();
        List<String> dwList = new ArrayList<>();
        List<String> rwList = new ArrayList<>();
        List<HashMap<String, String>> mps = new ArrayList<>();
        for (int i = 0; i < sheets; i++) {
            Sheet sheet = workbook.getSheetAt(i);
            int rowSize = sheet.getPhysicalNumberOfRows();
            for (int j = 0; j < rowSize; j++) {
                if (j == 0) continue;
                Row row = sheet.getRow(j);
                int cellSize = row.getPhysicalNumberOfCells();
                HashMap<String, String> mp = new HashMap<>();
                String cd = "";
                String dw = "";
                String rw = "";
                if (row.getCell(0) != null) {
                    row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
                    Cell cell0 = row.getCell(0);
                    cd = cell0.getStringCellValue();
                    mp.put("朝代", cd);
                }
                if (row.getCell(1) != null) {
                    row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                    Cell cell1 = row.getCell(1);
                    dw = cell1.getStringCellValue();
                    if (StringUtil.isBlank(dw)) {
                        dw = cd;
                    }
                    mp.put("政府机构", dw);
                }
                if (row.getCell(2) != null) {
                    row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
                    Cell cell2 = row.getCell(2);
                    rw = cell2.getStringCellValue();
                    mp.put("官职", rw);
                }
                mps.add(mp);
            }
        }
        in.close();
        cdList = mps.stream().map(n -> n.get("朝代")).distinct().collect(Collectors.toList());

        if (cdList == null || cdList.size() == 0) return;
        for (String m : cdList) {
            CategoryNode submitItem = new CategoryNode();
            submitItem.setCategoryNodeName(m);
            submitItem.setParentid(2);
            submitItem.setLevel(1);
            submitItem.setIsLeaf(0);
            submitItem.setFormula("");
            Integer id = insertCategoryNode(submitItem);
            dwList = mps.stream().filter(n -> n.get("朝代").equals(m)).map(n -> n.get("政府机构")).distinct().collect(Collectors.toList());
            for (String dw : dwList) {
                CategoryNode submitItem2 = new CategoryNode();
                submitItem2.setCategoryNodeName(dw);
                submitItem2.setParentid(id);
                submitItem2.setLevel(2);
                submitItem.setIsLeaf(0);
                submitItem2.setFormula("");
                Integer dwid = insertCategoryNode(submitItem2);
                List<HashMap<String, String>> gzList=mps.stream().filter(n -> n.get("朝代").equals(m) && n.get("政府机构").equals(dw)).collect(Collectors.toList());
                rwList = gzList.stream().map(n -> n.get("官职")).distinct().collect(Collectors.toList());
                for (String rw : rwList) {
                    CategoryNode submitItem3 = new CategoryNode();
                    submitItem3.setCategoryNodeName(rw);
                    submitItem3.setParentid(dwid);
                    submitItem3.setLevel(3);
                    submitItem.setIsLeaf(1);
                    submitItem3.setFormula(String.format("主题=\\'%s\\'", rw));
                    insertCategoryNode(submitItem3);
                }
            }
        }
    }
    @Test
    public void createCd() throws IOException {
        String filePath = "D:\\项目\\数字人文\\数字人文分类朝代.xlsx";
        File file = new File(filePath);
        String fileName = file.getName();
        InputStream in = null;
        in = new FileInputStream(file);
        Workbook workbook = null;
        if (ExcelUtil.isExcel2007(fileName)) {
            workbook = new XSSFWorkbook(in);
        } else {
            workbook = new HSSFWorkbook(in);
        }
        // 有多少个sheet
        int sheets = workbook.getNumberOfSheets();
        List<String> cdList = new ArrayList<>();
        List<String> dwList = new ArrayList<>();
        List<String> rwList = new ArrayList<>();
        List<HashMap<String, String>> mps = new ArrayList<>();
        for (int i = 0; i < sheets; i++) {
            Sheet sheet = workbook.getSheetAt(i);
            int rowSize = sheet.getPhysicalNumberOfRows();
            for (int j = 0; j < rowSize; j++) {
                if (j == 0) continue;
                Row row = sheet.getRow(j);
                int cellSize = row.getPhysicalNumberOfCells();
                HashMap<String, String> mp = new HashMap<>();
                String cd = "";
                String dw = "";
                String rw = "";
                if (row.getCell(0) != null) {
                    row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
                    Cell cell0 = row.getCell(0);
                    cd = cell0.getStringCellValue();
                    mp.put("朝代", cd);
                }
                mps.add(mp);
            }
        }
        in.close();
        cdList = mps.stream().map(n -> n.get("朝代")).distinct().collect(Collectors.toList());

        if (cdList == null || cdList.size() == 0) return;
        for (String m : cdList) {
            CategoryNode submitItem = new CategoryNode();
            submitItem.setCategoryNodeName(m);
            submitItem.setParentid(3);
            submitItem.setLevel(1);
            submitItem.setIsLeaf(1);
            submitItem.setFormula(String.format("主题=\\'%s\\'", m));
            Integer id = insertCategoryNode(submitItem);
        }
    }
    @Test
    public void createFormula() throws IOException {
        //updateFormula("0001");
        //updateFormula("0002");
        updateFormula("0003");
    }
    private void updateFormula(String code){
        String cq=String.format("select CategoryNodeName,CategoryNodeCode,CategoryNodeID,`level` from CategoryNode where CategoryNodeCode like('%s') and  (formulaLunwen is null or formulaLunwen='' )",code+"%");
        List<Map<String, Object>> maps= mysqlJdbcTemplate.queryForList(cq);
        for (Map<String, Object> map : maps) {
            String CategoryNodeName=map.get("CategoryNodeName").toString();
            String CategoryNodeCode=map.get("CategoryNodeCode").toString();
            String CategoryNodeID=map.get("CategoryNodeID").toString();
            String level=map.get("level").toString();
            String cq2=String.format("select distinct(CategoryNodeName) as name from CategoryNode where CategoryNodeCode like('%s') and  CategoryNodeID!=%s and level=3",CategoryNodeCode+"%",CategoryNodeID);
            List<Map<String, Object>> map2s= mysqlJdbcTemplate.queryForList(cq2);
            List<String> names=map2s.stream().map(n->String.format("\\'%s\\'",n.get("name").toString())).collect(Collectors.toList());
            String words=String.join("+",names);
            String updateSql = String.format("update CategoryNode set formulaLunwen='%s' where CategoryNodeID=%s", String.format(" 主题=%s",words), CategoryNodeID);
            mysqlJdbcTemplate.execute(updateSql);
        }

    }
    private Integer insertCategoryNode(CategoryNode submitItem) {
        String sql = String.format(" insert into CategoryNode " +
                        "(parentid,level,IsLeaf,Status,CreateUser,CreateTime,ModifyUser,ModifyTime,CategoryTypeID,CategoryNodeName,formulaLunwen)" +
                        " values(%s,%s,%s,%s,'%s','%s','%s','%s',%s,'%s','%s')",
                submitItem.getParentid(), submitItem.getLevel(), submitItem.getIsLeaf(), 1, "sa", DateUtil.getNowDateHms(), "sa", DateUtil.getNowDateHms(),
                0, submitItem.getCategoryNodeName(), submitItem.getFormula()
        );
        KeyHolder keyHolder = new GeneratedKeyHolder();
        mysqlJdbcTemplate.update(new PreparedStatementCreator() {
                                     @Override
                                     public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
                                         PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                                         return ps;
                                     }
                                 },
                keyHolder);
        Integer id = keyHolder.getKey().intValue();
        if(submitItem.getParentid()==0||submitItem.getParentid()==null){
            String code=String.format("%04d", id);
            String updateSql = String.format("update CategoryNode set CategoryNodeCode='%s' where CategoryNodeID=%s", code, id);
            mysqlJdbcTemplate.execute(updateSql);
            System.out.println(String.format("%s_%s",code,submitItem.getCategoryNodeName()));
        }else{
            String cq=String.format("select CategoryNodeCode from CategoryNode where CategoryNodeID=%s",submitItem.getParentid());
            Map<String, Object> mp=mysqlJdbcTemplate.queryForMap(cq);
            if(mp.get("CategoryNodeCode")!=null){
                String code=mp.get("CategoryNodeCode")+String.format("%04d", id);
                String updateSql = String.format("update CategoryNode set CategoryNodeCode='%s' where CategoryNodeID=%s", code, id);
                mysqlJdbcTemplate.execute(updateSql);
                System.out.println(String.format("%s_%s",code,submitItem.getCategoryNodeName()));
            }
        }

        return id;
    }
    @Test
 public  void createCtwhZT() throws IOException{
    String level0="select code,name,isleaf from ctwhcategory where grade=1";
     List<Map<String, Object>> maps= ctwhJdbcTemplate.queryForList(level0);
     for (Map<String, Object> map : maps) {
         String name = map.get("name").toString();
         String code = map.get("code").toString();
         CategoryNode submitItem = new CategoryNode();
         submitItem.setCategoryNodeName(name);
         submitItem.setParentid(0);
         submitItem.setLevel(0);
         submitItem.setIsLeaf(0);
         String format=String.format("主题=\\'%s\\'",name);
         submitItem.setFormula(format);
         Integer id = insertCategoryNode(submitItem);
         createChild(code,1,id);
     }
 }
 public void createChild(String code,int level,int pid){
     String level0=String.format("select code,name,isleaf from ctwhcategory where parentcode='%s' and code!='%s'",code,code);
     List<Map<String, Object>> maps= ctwhJdbcTemplate.queryForList(level0);
     if(maps!=null&&maps.size()>0){
         for (Map<String, Object> map : maps) {
             String nodeCode=map.get("code").toString();
             String nodeName=map.get("name").toString();
             String isleaf=map.get("isleaf").toString();
             CategoryNode submitItem = new CategoryNode();
             submitItem.setCategoryNodeName(nodeName);
             submitItem.setParentid(pid);
             submitItem.setLevel(level);
             submitItem.setIsLeaf(Integer.parseInt(isleaf));
             String format=String.format("主题=\\'%s\\'",nodeName);
             submitItem.setFormula(format);
             Integer id = insertCategoryNode(submitItem);
             if(isleaf.equals("1")) continue;
             createChild(nodeCode,level+1,id);
         }
     }
 }
    @Test
    public  void updateSortCode() throws IOException{
        String level0="select c_dynasty_chn as cd,c_sort as sortcode from `朝代_简` ORDER BY c_sort ";
        List<Map<String, Object>> maps= szrwJdbcTemplate.queryForList(level0);
        for (Map<String, Object> map : maps) {
            Object mp=map.get("cd");
            Object mp2=map.get("sortcode");
            if(mp==null||mp2==null) continue;
            String cd = map.get("cd").toString();
            String sortcode = new BigDecimal(map.get("sortcode").toString()).stripTrailingZeros().toPlainString();
            String sql=String.format("update categorynode set sortCode=%s where CategoryNodeName='%s' and CategoryTypeID=1",sortcode,cd);
            mysqlJdbcTemplate.execute(sql);
        }
    }
    //古代名人分类大辞典目录-修改版
    @Test
    public void createrwcd() throws IOException {
        String filePath = "D:\\项目\\数字人文\\古代名人分类大辞典目录-修改版.xlsx";
        File file = new File(filePath);
        String fileName = file.getName();
        InputStream in = null;
        in = new FileInputStream(file);
        Workbook workbook = null;
        if (ExcelUtil.isExcel2007(fileName)) {
            workbook = new XSSFWorkbook(in);
        } else {
            workbook = new HSSFWorkbook(in);
        }
        // 有多少个sheet
        int sheets = workbook.getNumberOfSheets();
        List<String> cdList = new ArrayList<>();
        List<String> dwList = new ArrayList<>();
        List<String> rwList = new ArrayList<>();
        List<String> zjList2 = new ArrayList<>();
        List<HashMap<String, String>> mps = new ArrayList<>();
        int pid=103125;
        for (int i = 0; i < sheets; i++) {
            Sheet sheet = workbook.getSheetAt(i);
            String sheetName=sheet.getSheetName();
            CategoryNode submitItem = new CategoryNode();
            submitItem.setCategoryNodeName(sheetName);
            submitItem.setParentid(pid);
            submitItem.setLevel(1);
            submitItem.setIsLeaf(0);
            submitItem.setFormula("");
            Integer id = insertCategoryNode(submitItem);
            int rowSize = sheet.getPhysicalNumberOfRows();
            for (int j = 0; j < rowSize; j++) {
                Row row = sheet.getRow(j);
                int cellSize = row.getPhysicalNumberOfCells();
                HashMap<String, String> mp = new HashMap<>();
                String cd = "";
                String dw = "";
                String rw = "";
                String rw2 = "";
                mp.put("sheetname", sheetName);
                if (row.getCell(0) != null) {
                    row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
                    Cell cell0 = row.getCell(0);
                    cd = cell0.getStringCellValue().trim();
                    mp.put("cell0", cd);
                }
                if (row.getCell(1) != null) {
                    row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                    Cell cell1 = row.getCell(1);
                    dw = cell1.getStringCellValue().trim();
                    if (StringUtil.isBlank(dw)) {
                        dw = cd;
                    }
                    mp.put("cell1", dw);
                }
                if (row.getCell(2) != null) {
                    row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
                    Cell cell2 = row.getCell(2);
                    rw2 = cell2.getStringCellValue().trim();
                    if (StringUtil.isBlank(rw2)) {
                        rw2 = cd;
                    }
                    mp.put("cell2", rw2);
                }else{
                    mp.put("cell2", "");
                }
                mps.add(mp);
            }
            cdList = mps.stream().filter(n->n.get("sheetname").equals(sheetName)).map(n -> n.get("cell0")).distinct().collect(Collectors.toList());
            if (cdList == null || cdList.size() == 0) return;
            for (String m : cdList) {
                CategoryNode submitItem2 = new CategoryNode();
                submitItem2.setCategoryNodeName(m);
                submitItem2.setParentid(id);
                submitItem2.setLevel(2);
                submitItem2.setIsLeaf(0);
                submitItem2.setFormula("");
                Integer id2 = insertCategoryNode(submitItem2);
                List<HashMap<String, String>> gzList=mps.stream().filter(n -> n.get("sheetname").equals(sheetName)&&n.get("cell0").equals(m)).collect(Collectors.toList());
                rwList = gzList.stream().map(n -> n.get("cell1")).distinct().collect(Collectors.toList());
                for (String rw : rwList) {
                    CategoryNode submitItem3 = new CategoryNode();
                    submitItem3.setCategoryNodeName(rw);
                    submitItem3.setParentid(id2);
                    submitItem3.setLevel(3);

                    if(!sheetName.equals("宗教部")) {
                        submitItem3.setIsLeaf(1);
                        submitItem3.setFormula(String.format("主题=\\'%s\\'", rw));
                    }else{
                        submitItem3.setIsLeaf(0);
                        submitItem3.setFormula("");
                    }
                    int id3=insertCategoryNode(submitItem3);
                    if(sheetName.equals("宗教部")){
                        List<HashMap<String, String>> zjList=mps.stream().filter(n -> n.get("sheetname").equals(sheetName)&&n.get("cell0").equals(m)&&n.get("cell1").equals(rw)).collect(Collectors.toList());
                        zjList2 = zjList.stream().map(n -> n.get("cell2")).distinct().collect(Collectors.toList());
                        for (String zjr : zjList2) {
                            CategoryNode submitItem4 = new CategoryNode();
                            submitItem4.setCategoryNodeName(zjr);
                            submitItem4.setParentid(id3);
                            submitItem4.setLevel(4);
                            submitItem4.setIsLeaf(1);
                            submitItem4.setFormula(String.format("主题=\\'%s\\'", zjr));
                            insertCategoryNode(submitItem4);
                        }

                    }
                }
            }
        }
        in.close();

    }
    @Test
    public void createzjbu() throws IOException {
        String filePath = "D:\\项目\\数字人文\\古代名人分类大辞典目录-修改版.xlsx";
        File file = new File(filePath);
        String fileName = file.getName();
        InputStream in = null;
        in = new FileInputStream(file);
        Workbook workbook = null;
        if (ExcelUtil.isExcel2007(fileName)) {
            workbook = new XSSFWorkbook(in);
        } else {
            workbook = new HSSFWorkbook(in);
        }
        // 有多少个sheet
        int sheets = workbook.getNumberOfSheets();
        List<String> cdList = new ArrayList<>();
        List<String> dwList = new ArrayList<>();
        List<String> rwList = new ArrayList<>();
        List<String> zjList2 = new ArrayList<>();
        List<HashMap<String, String>> mps = new ArrayList<>();
        int pid=103125;
        for (int i = 0; i < sheets; i++) {
            Sheet sheet = workbook.getSheetAt(i);
            String sheetName=sheet.getSheetName();
            if(!sheetName.equals("宗教部")) continue;
            Integer id = 231635;
            int rowSize = sheet.getPhysicalNumberOfRows();
            for (int j = 0; j < rowSize; j++) {
                Row row = sheet.getRow(j);
                int cellSize = row.getPhysicalNumberOfCells();
                HashMap<String, String> mp = new HashMap<>();
                String cd = "";
                String dw = "";
                String rw = "";
                String rw2 = "";
                mp.put("sheetname", sheetName);
                if (row.getCell(0) != null) {
                    row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
                    Cell cell0 = row.getCell(0);
                    cd = cell0.getStringCellValue().trim();
                    mp.put("cell0", cd);
                }
                if (row.getCell(1) != null) {
                    row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                    Cell cell1 = row.getCell(1);
                    dw = cell1.getStringCellValue().trim();
                    if (StringUtil.isBlank(dw)) {
                        dw = cd;
                    }
                    mp.put("cell1", dw);
                }
                if (row.getCell(2) != null) {
                    row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
                    Cell cell2 = row.getCell(2);
                    rw2 = cell2.getStringCellValue().trim();
                    if (StringUtil.isBlank(rw2)) {
                        rw2 = cd;
                    }
                    mp.put("cell2", rw2);
                }else{
                    mp.put("cell2", "");
                }
                mps.add(mp);
            }
            cdList = mps.stream().filter(n->n.get("sheetname").equals(sheetName)).map(n -> n.get("cell0")).distinct().collect(Collectors.toList());
            if (cdList == null || cdList.size() == 0) return;
            for (String m : cdList) {
                CategoryNode submitItem2 = new CategoryNode();
                submitItem2.setCategoryNodeName(m);
                submitItem2.setParentid(id);
                submitItem2.setLevel(2);
                submitItem2.setIsLeaf(0);
                submitItem2.setFormula("");
                Integer id2 = insertCategoryNode(submitItem2);
                List<HashMap<String, String>> gzList=mps.stream().filter(n -> n.get("sheetname").equals(sheetName)&&n.get("cell0").equals(m)).collect(Collectors.toList());
                rwList = gzList.stream().map(n -> n.get("cell1")).distinct().collect(Collectors.toList());
                for (String rw : rwList) {
                    CategoryNode submitItem3 = new CategoryNode();
                    submitItem3.setCategoryNodeName(rw);
                    submitItem3.setParentid(id2);
                    submitItem3.setLevel(3);
                    submitItem3.setIsLeaf(0);
                    submitItem3.setFormula("");
                    int id3=insertCategoryNode(submitItem3);
                    List<HashMap<String, String>> zjList=mps.stream().filter(n -> n.get("sheetname").equals(sheetName)&&n.get("cell0").equals(m)&&n.get("cell1").equals(rw)).collect(Collectors.toList());
                    zjList2 = zjList.stream().map(n -> n.get("cell2")).distinct().collect(Collectors.toList());
                    for (String zjr : zjList2) {
                        CategoryNode submitItem4 = new CategoryNode();
                        submitItem4.setCategoryNodeName(zjr);
                        submitItem4.setParentid(id3);
                        submitItem4.setLevel(4);
                        submitItem4.setIsLeaf(1);
                        submitItem4.setFormula(String.format("主题=\\'%s\\'", zjr));
                        insertCategoryNode(submitItem4);
                    }
                }
            }
        }
        in.close();

    }
}
